package com.yulian.repository.system_management;
 
import com.yulian.util.CommonUtils;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonObject;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

/**
 * 该类位于数据访问层，在该类的多个方法中，执行操作数据的sql语句，实现支付主体准入管理数据的增、删、查、改等功能，为业务逻辑层中的类PaymentAccessManager提供数据服务。
 * @author  
 *
 */ 
public class SystemMenuRepositoryImpl {
	final InternalLogger logger = Log4JLoggerFactory.getInstance(SystemMenuRepositoryImpl.class);
	    
	 
	/**
	 * 如果菜单项不是button类型，向系统菜单表system_menu插入新的数据
	 * 如果菜单项是button类型，同时要向权限表perms插入新的数据
	 * @param conn
	 * @param menu_code
	 * @param menu_name
	 * @param parent_code
	 * @param parent_name
	 * @param menu_path
	 * @param menu_icon
	 * @param is_leaf
	 * @param is_button
	 * @param create_user
	 * @return
	 */
	public Future<JsonObject> insertSystemMenu(SqlConnection conn,   String menu_code, String menu_name,  String parent_code, String parent_name,
			String menu_path, String menu_icon, int is_leaf, int is_button, int sort_order, String create_user){
		Promise<JsonObject> promise = Promise.promise();  
		JsonObject responseJSObj = new JsonObject();
		responseJSObj.put("code", 200).put("msg", "新增系统菜单: ");
		String sql1 = "INSERT INTO system_menu(menu_code, menu_name, parent_code, parent_name, menu_path, menu_icon, is_leaf, is_button, sort_order, create_user)"
				+ " values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)  RETURNING id" ;
		if(is_button == 0) {//不是按钮
			 conn.preparedQuery(sql1).execute(Tuple.of( menu_code,  menu_name, parent_code, parent_name, menu_path, menu_icon, is_leaf, is_button, sort_order, create_user), re -> { 
				 conn.close();
				 if(re.succeeded()){
					 if (re.result().rowCount() > 0) {
	                     // 取出更新记录的id
	                     long[] temp_id = new long[1];
	                     re.result().forEach(item -> {
	                         temp_id[0] = item.getLong("id");
	                     });
	                     responseJSObj.put("msg", responseJSObj.getString("msg")+"新增成功,记录id: " + temp_id[0]);
	                 } else { // 更新数据返回结果行数为0 
	                     responseJSObj.put("msg", responseJSObj.getString("msg")+"新增失败");
	                 }  
					 logger.info("insertSystemMenu, 成功: {}", re.result().rowCount());
					 promise.complete(responseJSObj);
				 }else{ 
					 logger.info("insertSystemMenu, 失败,失败原因:{}", re.cause().getMessage());		
					 promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/insert", "服务器处理异常", sql1)); 
				 }
			 });
		}else { 
			//当插入的菜单级别为按钮时，需要在perms表中添加 perm、perm_code, perm_code就取menu_code的值； 需要用到事务控制 
			String perm_name = getPerm(parent_name) + "_" + getButtonName(menu_name);
			String sql2 = "insert into perms(perm, perm_code) values ($1,$2)  RETURNING id";
			// Begin the transaction
			conn.begin()
			.compose(tx -> conn
					// Various statements
					.preparedQuery(sql2).execute( Tuple.of(perm_name, menu_code))
					.onComplete(re->{  //the result of the first query  
						if(re.succeeded()){  
							if (re.result().rowCount() > 0) {
			                     // 取出更新记录的id
			                     long[] temp_id = new long[1];
			                     re.result().forEach(item -> {
			                         temp_id[0] = item.getLong("id");
			                     });
			                     responseJSObj.put("msg", responseJSObj.getString("msg")+"新增权限成功,记录id: " + temp_id[0]);
			                 } else { // 更新数据返回结果行数为0 
			                     responseJSObj.put("msg", responseJSObj.getString("msg")+"新增权限失败");
			                 }  
							 logger.info("insert_perms, 成功: {}", re.result().rowCount()); 
				        }else{
				        	logger.info("insert_perms,失败,失败原因:" + re.cause().getMessage());	 
				        	promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/insert", "服务器处理异常", sql2));
				        }
					})
					.compose(res2 -> conn 
							.preparedQuery(sql1).execute(Tuple.of( menu_code, menu_name, parent_code, parent_name, menu_path, menu_icon, is_leaf, is_button, sort_order,create_user))
							.onComplete(re->{  //the result of the second query 
								if(re.succeeded()){  
									 if (re.result().rowCount() > 0) {
					                     // 取出更新记录的id
					                     long[] temp_id = new long[1];
					                     re.result().forEach(item -> {
					                         temp_id[0] = item.getLong("id");
					                     });
					                     responseJSObj.put("msg", responseJSObj.getString("msg")+"; 新增系统菜单成功,记录id: " + temp_id[0]);
					                 } else { // 更新数据返回结果行数为0 
					                     responseJSObj.put("msg", responseJSObj.getString("msg")+"; 新增系统菜单失败");
					                 }  
									 logger.info("insert_system_menu, 成功: {}", re.result().rowCount());
									 promise.complete(responseJSObj);
									 
						        }else{
						        	 logger.info("insertSystemMenu, 失败,失败原因:{}", re.cause().getMessage());		
									 promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/insert", "服务器处理异常", sql1)); 
						        }
							}))  
					// Commit the transaction	
					.compose(res3 -> tx.commit() ) )
					// Return the connection to the pool
			.eventually(v -> conn.close())
			.onSuccess(v -> logger.info("insert_perms and insert_system_menu, Transaction succeeded"))
			.onFailure(err -> logger.info("insert_perms and insert_system_menu, Transaction failed: " + err.getMessage())); 
		} 
	    return promise.future();
	}
	
	 
	/**
	 * 更新数据
	 * @param conn
	 * @param id 
	 * @param menu_name
	 * @param parent_code
	 * @param parent_name
	 * @param menu_path
	 * @param menu_icon
	 * @param is_leaf
	 * @param is_button
	 * @param update_user
	 * @return
	 */
	public Future<JsonObject> updateSystemMenu(SqlConnection conn, long id, String menu_code, String menu_name,  String parent_code, String parent_name,
			String menu_path, String menu_icon, int is_leaf, int is_button, int sort_order,String update_user){
		Promise<JsonObject> promise = Promise.promise();  
		JsonObject responseJSObj = new JsonObject();
		responseJSObj.put("code", 200).put("msg", "更新系统菜单和权限: ");
		String sql1 = "update system_menu set menu_name = $1, parent_code=$2, parent_name=$3, menu_path=$4, menu_icon=$5, is_leaf=$6, is_button=$7, sort_order = $8, update_user = $9 where id=$10  RETURNING id" ;
		if(is_button == 0) { //如果是非按钮级别，只需要更新 system_menu
			conn
			.preparedQuery(sql1).execute(Tuple.of( menu_name, parent_code, parent_name, menu_path, menu_icon, is_leaf, is_button, sort_order, update_user, id) , re -> {
				//Release the connection to the pool
		        conn.close();
		        if(re.succeeded()){
					 if (re.result().rowCount() > 0) {
	                     // 取出更新记录的id
	                     long[] temp_id = new long[1];
	                     re.result().forEach(item -> {
	                         temp_id[0] = item.getLong("id");
	                     });
	                     responseJSObj.put("msg", responseJSObj.getString("msg")+"更新系统菜单成功,记录id: " + temp_id[0]);
	                 } else { // 更新数据返回结果行数为0 
	                     responseJSObj.put("msg", responseJSObj.getString("msg")+"更新系统菜单失败");
	                 }  
					 logger.info("updateSystemMenu, 成功: {}", re.result().rowCount());
					 promise.complete(responseJSObj);
				 }else{ 
					 logger.info("updateSystemMenu, 失败,失败原因:{}", re.cause().getMessage());		
					 promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/insert", "服务器处理异常", sql1)); 
				 } 
			});
		} else { 
			//当更新的菜单级别为按钮时，需要在perms表中更新 perm,所以需要用到事务控制 ;其中getPerm是根据parent_name 查询菜单英文名，
			String perm_name = getPerm(parent_name) + "_" + getButtonName(menu_name);
			String sql2 = "update perms set perm = $1 where perm_code = $2   RETURNING id"; 
			// Begin the transaction
			conn.begin()
			.compose(tx -> conn
					// Various statements
					.preparedQuery(sql2).execute( Tuple.of(perm_name, menu_code) )
					.onComplete(re->{  //the result of the first query 
						if(re.succeeded()){  
							if (re.result().rowCount() > 0) {
			                     // 取出更新记录的id
			                     long[] temp_id = new long[1];
			                     re.result().forEach(item -> {
			                         temp_id[0] = item.getLong("id");
			                     });
			                     responseJSObj.put("msg", responseJSObj.getString("msg")+"更新权限成功,记录id: " + temp_id[0]);
			                 } else { // 更新数据返回结果行数为0 
			                     responseJSObj.put("msg", responseJSObj.getString("msg")+"更新权限失败");
			                 }  
							 logger.info("update_perms, 成功: {}", re.result().rowCount()); 
				        }else{
				        	logger.info("update_perms,失败,失败原因:" + re.cause().getMessage());	 
				        	promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/update", "服务器处理异常", sql2));
				        } 
					})
					.compose(res2 -> conn 
							.preparedQuery(sql1).execute(Tuple.of( menu_name, parent_code, parent_name, menu_path, menu_icon, is_leaf, is_button, sort_order, update_user, id))
							.onComplete(re->{  //the result of the second query 
								if(re.succeeded()){  
									 if (re.result().rowCount() > 0) {
					                     // 取出更新记录的id
					                     long[] temp_id = new long[1];
					                     re.result().forEach(item -> {
					                         temp_id[0] = item.getLong("id");
					                     });
					                     responseJSObj.put("msg", responseJSObj.getString("msg")+"; 更新系统菜单成功,记录id: " + temp_id[0]);
					                 } else { // 更新数据返回结果行数为0 
					                     responseJSObj.put("msg", responseJSObj.getString("msg")+"; 更新系统菜单失败");
					                 }  
									 logger.info("update_system_menu, 成功: {}", re.result().rowCount());
									 promise.complete(responseJSObj);
									 
						        }else{
						        	 logger.info("update_system_menu, 失败,失败原因:{}", re.cause().getMessage());		
									 promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/update", "服务器处理异常", sql1)); 
						        } 
							}))  
					// Commit the transaction	
					.compose(res3 -> tx.commit() ) )
					// Return the connection to the pool
				    .eventually(v -> conn.close())
				    .onSuccess(v -> logger.info("update_perms and update_system_menu, Transaction succeeded"))
				    .onFailure(err -> logger.info("update_perms and update_system_menu, Transaction failed: " + err.getMessage()));  
			
		}  
	    return promise.future();
	} 
	
	/**
	 * 物理删除
	 * @param conn
	 * @param is_delete
	 * @param id_list
	 * @param update_user
	 * @return
	 */
	public Future<JsonObject> physicalDeleteSystemMenu(SqlConnection conn, String id_list, String update_user){
		Promise<JsonObject> promise = Promise.promise();  
		JsonObject responseJSObj = new JsonObject();
		responseJSObj.put("code", 200).put("msg", "新增系统菜单: "); 
		String sql1= "delete from system_menu where id = any(array" +  id_list + ") RETURNING id" ;  
		String sql2 = "delete from perms  where perm_code = any(array( select menu_code from system_menu where id = any(array" +  id_list + ")))"; 
		// Begin the transaction
		conn.begin()
		.compose(tx -> conn
				// Various statements
				.preparedQuery(sql2).execute()
				.onComplete(re->{  //the result of the first query 
					if(re.succeeded()){  
						if (re.result().rowCount() > 0) { 
		                     responseJSObj.put("msg", responseJSObj.getString("msg")+"物理删除权限成功,记录id: " +id_list);
		                 } else { // 更新数据返回结果行数为0 
		                     responseJSObj.put("msg", responseJSObj.getString("msg")+"物理删除权限失败");
		                 }  
						 logger.info("physical_delete_perms, 成功: {}", re.result().rowCount()); 
			        }else{
			        	logger.info("physical_delete_perms,失败,失败原因:" + re.cause().getMessage());	 
			        	promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/delete", "服务器处理异常", sql2));
			        }  
				})
				.compose(res2 -> conn 
						.preparedQuery(sql1)
						.execute()
						.onComplete(re->{  //the result of the second query 
							if(re.succeeded()){  
								 if (re.result().rowCount() > 0) { 
				                     responseJSObj.put("msg", responseJSObj.getString("msg")+"; 物理删除系统菜单成功,记录id: " + id_list);
				                 } else { // 更新数据返回结果行数为0 
				                     responseJSObj.put("msg", responseJSObj.getString("msg")+"; 物理删除系统菜单失败");
				                 }  
								 logger.info("physical_delete_system_menu, 成功: {}", re.result().rowCount());
								 promise.complete(responseJSObj);
								 
					        }else{
					        	 logger.info("physical_delete_system_menu, 失败,失败原因:{}", re.cause().getMessage());		
								 promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/update", "服务器处理异常", sql1)); 
					        }  
						}))  
				// Commit the transaction	
				.compose(res3 -> tx.commit() ) )
				// Return the connection to the pool
			    .eventually(v -> conn.close())
			    .onSuccess(v -> logger.info("physical_delete_perms and physical_delete_perms, Transaction succeeded"))
			    .onFailure(err -> logger.info("physical_delete_perms and physical_delete_perms, Transaction failed: " + err.getMessage()));  
 
	    return promise.future();
	}
	
	/**
	 * 逻辑删除，启用或停用;当某个菜单被停用，需要停用该菜单下面的所有子菜单 
	 * @param conn
	 * @param is_delete
	 * @param id_list
	 * @param update_user
	 * @return
	 */
	public Future<JsonObject> deleteSystemMenu(SqlConnection conn, int is_delete,String id_list, String update_user){ 
		Promise<JsonObject> promise = Promise.promise();  
		JsonObject responseJSObj = new JsonObject();
		String sql1= "update system_menu set is_delete = $1, update_user = $2 where id = any(array" +  id_list + ") RETURNING id" ;
		String sql2 = "update perms set is_delete = $1 where perm_code = any(array( select menu_code from system_menu where id = any(array" +  id_list + ")))"; 
		// Begin the transaction
		conn.begin()
		.compose(tx -> conn
				// Various statements
				.preparedQuery(sql2).execute( Tuple.of(is_delete) )
				.onComplete(re->{  //the result of the first query 
					if(re.succeeded()){  
						if (re.result().rowCount() > 0) { 
		                     responseJSObj.put("msg", responseJSObj.getString("msg")+"逻辑删除权限成功,记录id: " +id_list);
		                 } else { // 更新数据返回结果行数为0 
		                     responseJSObj.put("msg", responseJSObj.getString("msg")+"逻辑删除权限失败");
		                 }  
						logger.info("delete_perms, 成功: {}", re.result().rowCount()); 
			        }else{
			        	logger.info("delete_perms,失败,失败原因:" + re.cause().getMessage());	 
			        	promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/delete", "服务器处理异常", sql2));
			        }   
				})
				.compose(res2 -> conn 
						.preparedQuery(sql1).execute(Tuple.of(is_delete,update_user))
						.onComplete(re->{  //the result of the second query 
							if(re.succeeded()){  
								 if (re.result().rowCount() > 0) { 
				                     responseJSObj.put("msg", responseJSObj.getString("msg")+"逻辑删除系统菜单成功,记录id: " + id_list);
				                 } else { // 更新数据返回结果行数为0 
				                     responseJSObj.put("msg", responseJSObj.getString("msg")+"逻辑删除系统菜单失败");
				                 }  
								 logger.info("delete_system_menu, 成功: {}", re.result().rowCount());
								 promise.complete(responseJSObj);
								 
					        }else{
					        	 logger.info("delete_system_menu, 失败,失败原因:{}", re.cause().getMessage());		
								 promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "/web/system_menu/delete", "服务器处理异常", sql1)); 
					        }  
						}))  
				// Commit the transaction	
				.compose(res3 -> tx.commit() ) )
				// Return the connection to the pool
			    .eventually(v -> conn.close())
			    .onSuccess(v -> logger.info("delete_perms and delete_system_menu, Transaction succeeded"))
			    .onFailure(err -> logger.info("delete_perms and delete_system_menu, Transaction failed: " + err.getMessage()));  
 
	    return promise.future();
	}
	
	/**
	 * 根据父亲级菜单名为perm命名
	 * @param parent_name
	 * @return
	 */
	public String getPerm(String parent_name) {
		String perm="";
		switch(parent_name) { 
		//基础资料
		case "供应商基本信息":  
			perm = "supplier_info";
			break;
		case "供应商结算账号信息":  
			perm = "supplier_settlement_account";
			break;
		case "供应商开票信息":  
			perm = "supplier_invoice";
			break;
		case "商品名系统录入参数在线管控表":  
			perm = "goods_parameter";
			break;
		case "供应链商品聚集区商品池包装物准入管理":  
			perm = "goods_package";
			break;
		case "供应链商品聚集区商品池":  
			perm = "gather_area_goods_cost";
			break;
		case "供应商商品池":  
			perm = "supplier_goods_cost";
			break;
		case "品种商品比价基础参数设置管理":  
			perm = "goods_price_bid_parameter";
			break;
		case "标准线路干线运费分摊设置管理":  
			perm = "mainline_freight_allocation";
			break;
		case "承运司机准入管理":  
			perm = "carrier_driver" ;	
			break;
		case "支付主体准入管理":  
			perm = "payment_entity" ; 
			break;
		case "合伙人提成比例设置管理":  
			perm = "partner_commission_ratio" ; 
			break;
		case "合伙人报损分摊比例设置管理":  
			perm = "partner_loss_ratio" ; 
			break;  
		case "用户管理":  
			perm = "user" ; 
			break;  	
		case "角色管理":  
			perm = "role" ; 
			break;  
		case "权限管理":  
			perm = "role" ; 
			break;  
		case "操作日志":  
			perm = "operation_log" ; 
			break;  
		//功能模块
			
			
		//设置管理
		case "供应商等级":   
			perm = "supplier_grade" ; 
			break;
		case "年龄段":   
			perm = "age_group" ; 
			break;	
		case "从业年限":   
			perm = "operation_period" ; 
			break;
		case "账户类型":   
			perm = "account_type" ; 
			break;
		case "发票类型":   
			perm = "invoice_type" ; 
			break;
		case "开票周期":   
			perm = "invoice_cycle" ; 
			break;
		case "商品销量等级":   
			perm = "goods_sales_grade" ; 
			break;
		case "计价方式":   
			perm = "goods_price_mode" ; 
			break;
		case "商品规格":   
			perm = "goods_specification" ; 
			break;
		case "外包装物类型":   
			perm = "outer_package_type" ; 
			break;
		case "外包装物型号":   
			perm = "outer_package_mode" ; 
			break;
		case "内包装物类型":   
			perm = "inner_package_type" ; 
			break;
		case "包装物特征类型":   
			perm = "package_charac_type" ; 
			break;
		case "原料计价方式":   
			perm = "material_price_mode" ; 
			break;
		case "干线运输标准线路名":   
			perm = "mainline_route_name" ; 
			break; 
			
		} 
		return perm;
	}
	
	public String getButtonName(String menu_name) {
		String perm="";
		switch(menu_name) { 
		case "查询":  
			perm = "get_records"; 
			break;
		case "新增":  
			perm = "insert_or_update"; 
			break;
		case "批量导入":  
			perm = "batch_import"; 
			break;
		case "编辑":  
			perm = "insert_or_update";  
			break;
		case "删除":  //物理删除
			perm = "physical_delete"; 
			break;
		case "准入":  
			perm = "admit"; 
			break;
		case "生效":  //逻辑删除
			perm = "delete"; 
			break;
		case "导出":  
			perm = "export"; 
			break;
		case "查看供应商结算账号信息":  
			perm = "query_supplier_settlement_account"; 
			break;
		case "查看供应商开票信息":  
			perm = "query_supplier_invoice"; 
			break;
		} 
		return perm;
	}

}
